package com.example.stag;

import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.util.ArrayList;

public class CommoditySQLOperation {
    private static final String SQLURL = "jdbc:mysql://112.74.110.106:3306/";
    private static final String databaseName = "db1";
    private static final String tableName = "CommodityInformation";
    private static final String databaseUsername = "root";
    private static final String databasePassword = "111111";
    private static Connection conn = null;
    private static PreparedStatement pst = null;
    private static long nowTime = 0;
    private static final long timeCycle = 14400000;

    public static boolean connect() {
        if (System.currentTimeMillis() - nowTime > timeCycle) {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                conn = DriverManager.getConnection(SQLURL + databaseName, databaseUsername, databasePassword);
                nowTime = System.currentTimeMillis();
                return true;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        } else {
            return false;
        }
    }

    static void close() {
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    static int updateName(String id, String newName) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set name=? where id=?;");
            pst.setString(1, newName);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateBatch(String id, String batch) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set batch=? where id=?;");
            pst.setString(1, batch);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateDealer(String id, String dealer) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set dealer=? where id=?;");
            pst.setString(1, dealer);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateDescription(String id, String description) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set description=? where id=?;");
            pst.setString(1, description);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateSuggestion(String id, String suggestion) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set suggestion=? where id=?;");
            pst.setString(1, suggestion);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateCirculationStatus(String id, String CirculationStatus) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set CirculationStatus=? where id=?;");
            pst.setString(1, CirculationStatus);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateSaleStatus(String id, String SaleStatus) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set SaleStatus=? where id=?;");
            pst.setString(1, SaleStatus);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int updateOpenStatus(String id, String OpenStatus) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set OpenStatus=? where id=?;");
            pst.setString(1, OpenStatus);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int insertCommodity(String id, String name, String batch, String dealer, String description, String suggestion, String CirculationStatus
            , String SaleStatus, String OpenStatus, String ProductionTime, String ProductionAddress, String UserReviews) {
        try {
            pst = conn.prepareStatement("insert into " + tableName + " values(?,?,?,?,?,?,?,?,?,?,?,?);");
            pst.setString(1, id);
            pst.setString(2, name);
            pst.setString(3, batch);
            pst.setString(4, dealer);
            pst.setString(5, description);
            pst.setString(6, suggestion);
            pst.setString(7, CirculationStatus);
            pst.setString(8, SaleStatus);
            pst.setString(9, OpenStatus);
            pst.setString(10, ProductionTime);
            pst.setString(11, ProductionAddress);
            pst.setString(12, UserReviews);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static int deleteCommodity(String id) {
        try {
            pst = conn.prepareStatement("delete from " + tableName + " where id=?;");
            pst.setString(1, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    static ResultSet searchByBatch(String batch) {
        try {
            pst = conn.prepareStatement("select * from " + tableName + " where batch=?");
            pst.setString(1, batch);
            pst.execute();
            return pst.getResultSet();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    static ResultSet searchById(String id) {
        try {
            pst = conn.prepareStatement("select * from " + tableName + " where id=?");
            pst.setString(1, id);
            pst.execute();
            return pst.getResultSet();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static int updateUserReviews(String id, String userReviews) {
        try {
            pst = conn.prepareStatement("update " + tableName + " set userReviews=? where id=?;");
            pst.setString(1, userReviews);
            pst.setString(2, id);
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static String searchSales(String name) {
        try {
            pst = conn.prepareStatement("SELECT COUNT(*) FROM " + tableName + " WHERE Name=?;");
            pst.setString(1, name);
            pst.execute();
            ResultSet resultSet = pst.getResultSet();
            if (resultSet.next()) {
                return resultSet.getString("COUNT(*)");
            } else {
                return "0";
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return "";
        }
    }

    public static double[] getAppraise(String name) {
        try {
            int high = 0;
            int mid = 0;
            int low = 0;
            pst = conn.prepareStatement("SELECT COUNT(*) FROM " + tableName + " WHERE Name=? and UserReviews=?;");
//            pst = conn.prepareStatement("SELECT * FROM " + tableName + " WHERE `Name`=?;");
            pst.setString(1, name);
            pst.setString(2, "youxiu");
            pst.execute();
            ResultSet resultSet = pst.getResultSet();
            System.out.println(name);
            if (resultSet.next()) {
                high = Integer.parseInt(resultSet.getString("COUNT(*)"));
            }
            System.out.println(resultSet.getString("COUNT(*)"));


            pst.setString(1, name);
            pst.setString(2, "lianghao");
            pst.execute();
            resultSet = pst.getResultSet();
            if (resultSet.next()) {
                mid = Integer.parseInt(resultSet.getString("COUNT(*)"));
            }
            System.out.println(resultSet.getString("COUNT(*)"));

            pst.setString(1, name);
            pst.setString(2, "cha");
            pst.execute();
            resultSet = pst.getResultSet();
            if (resultSet.next()) {
                low = Integer.parseInt(resultSet.getString("COUNT(*)"));
            }
            System.out.println(resultSet.getString("COUNT(*)"));

            int sum = high + mid + low;
            double[] res = new double[3];
            res[0] = (double) (high) / sum;
            res[1] = (double) (mid) / sum;
            res[2] = (double) (low) / sum;
            return res;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }
}
